#!/usr/bin/env python
# -*- coding:utf-8 -*-
# Author:Zhangcl
from db.connect_db import engine,Students,Teacher,Classes,Class_choise,Class_record,Homework,Grade,Admin
from sqlalchemy.orm import sessionmaker
from sqlalchemy import and_, or_,func
session_connet = sessionmaker(bind=engine)
session = session_connet()
class Student_run(object):
    def __init__(self,user):
        self.user_qq = user
        obj_name = session.query(Students.name).filter(Students.qq==self.user_qq).first()
        self.user_name = obj_name
    def menu(self):
        choise_menu = '''
                \033[1;31m
                   ---------Welcome  %s----------
                   1. 提交作业
                   2. 查看成绩
                   3. 查看班级排名
                   4. 退出
                \033[0m

                ''' % self.user_name

        menu_dict = {
            '1': Student_run.put_homework,
            '2': Student_run.select_grade,
            '3': Student_run.view_rankings,
            '4': 'exit'
        }

        while True:
            print(choise_menu)
            choise = input('请选择>>>')
            if choise in menu_dict:
                if choise == '4':
                    exit()
                else:
                    menu_dict[choise](self)
            else:
                print('输入错误！')

    def put_homework(self):
        obj_class = session.query(Class_choise).filter(Class_choise.student_qq==self.user_qq).all()#查询该学生绑定了的课程列表
        for index,item in enumerate(obj_class):
            print(index,item.classes.name)
        class_choise = input('请选择班级>>>')
        if obj_class[int(class_choise)] and class_choise.isdigit():
            class_id = obj_class[int(class_choise)].class_id
            obj_homework = session.query(Homework).filter(and_(Homework.student_qq==self.user_qq,
                                                                                    Homework.class_id==class_id)).all()#查询对应课程的作业提交记录
            for i,f in enumerate(obj_homework):
                if f.status =='NO':
                    print(i,f.date,'未提交')
                if f.status =='YES':
                    print(i,f.date,'已提交')
            homework_chois = input('请选择提交的日期>>>')
            if homework_chois.isdigit():
                if obj_homework[int(homework_chois)]:
                    choise_date=obj_homework[int(homework_chois)].date
                    if obj_homework[int(homework_chois)].status=='NO':
                        homework=session.query(Homework).filter(and_(Homework.class_id == class_id,
                                                            Homework.date == choise_date,
                                                            Homework.student_qq == self.user_qq)).first()#
                        homework.status='YES'#将需要提交的课程对应的上课记录的作业提交状态更新为已提交
                        grade=session.query(Grade).filter(and_(Grade.class_id == class_id,
                                                         Grade.date == choise_date,
                                                        Grade.student_qq == self.user_qq)).first()
                        grade.status='YES'#同时修改成绩表该学生该条作业提交状态，以便教师进行修改
                        session.commit()
                        print('提交作业成功！')
                    else:
                        print('作业已提交，请勿重复提交！')
                else:
                    print('输入错误！')
            else:
                print('输入错误！')
        else:
            print('输入错误！')
    def select_grade(self):
        obj_class = session.query(Class_choise).filter(Class_choise.student_qq == self.user_qq).all()
        for index, item in enumerate(obj_class):
            print(index, item.classes.name)
        class_choise = input('请选择班级>>>')
        if class_choise.isdigit():
            if obj_class[int(class_choise)]:
                class_id = obj_class[int(class_choise)].class_id
                obj_grade = session.query(Grade).filter(and_(Grade.class_id==class_id,
                                                             Grade.student_qq==self.user_qq)).all()
                for i in obj_grade:
                    if i.status=='YES':
                        print(i.date,'已提交',i.grade)
                    if i.status=='NO':
                        print(i.date, '未提交', i.grade)
            else:
                print('输入错误！')
        else:
            print('输入错误！')
    def view_rankings(self):
        obj_class =session.query(Class_choise).filter(Class_choise.student_qq==self.user_qq).all()#查询该学生绑定的课程
        for index,i in enumerate(obj_class):
            print(index,i.classes.name)
        class_choise = input('请选择班级>>>')
        if class_choise.isdigit():
            if obj_class[int(class_choise)]:
                class_id=obj_class[int(class_choise)].class_id#获取需要的查询的课程ID
                obj_grade=session.query(Grade,func.sum(Grade.grade).label('grade_sum')).\
                    filter(Grade.class_id==class_id).\
                    group_by(Grade.student_qq).\
                    order_by(func.sum(Grade.grade).desc()).all() #按学生qq分组，获取到每个学生的总成绩，按总成绩的倒序进行排列
                for index,f in enumerate(obj_grade):
                    print(index+1,f[0].student.name,f[-1])#展示学生该课程总成绩排名

